Put your logo here!

Special Relation searches

Standard searches allow you to rapidly create data sets that are related by special identifier fields, such as the Subject_ID field. However, in some cases you may wish to perform a search using values from unrelated fields to create a "pseudo-relationship." For example, using the "audiograms with thresholds > 25 dB" set to define the set of "tympanograms which are related to those audiograms". In the previous example used for searching we found a set of subjects with "audiograms with thresholds > 25 dB" and "Type C tympanograms". We did not specify that the Type C tympanograms belong to the specific audiograms, just that they belong to the subject. It is possible that the subject had a Type B tympanogram on the same day they had their audiogram done and a Type C on another day. By executing a Special Relation query we can make sure the tympnaogram belongs to the audiogram by relating the [Audiogram]Related_Tympanogram_No field.

The Special Relation query form can be accessed by dragging-and-dropping a search set onto the Special Relation box at the bottom of the Search form. This form allows you to perform two types of searches: 1) searches comparing field values from one set of records with field values in all the available records in a table, and 2) searches performing operations on a set of records to select the records with the "minimum" or "maximum" values within subgroups of these records.

Let's work through the "find the tympanograms for the audiograms with thresholds > 25 dB" example to illustrate the first type of Special Relation query.

To perform a standard comparison query
  1. In the Appendices you would find information on the Audiogram table and, subsequently, the Related_Tympnaogram_No field. This field stores a long integer with the following values:

    • 0 = No related tympanogram
    • Tympnaogram_No of the related tympanogram
  2. Use the New Query form to create a new search set from the Audiogram table. Your search set is looking for audiograms with air thresholds > 25 dB as for the previou search example.
  3. We have a set of 14 audiograms. Now we want to find the tympanograms which are related to these audiograms. To do this we will perform a Special Relation search.

  4. Drag-and-drop this set on the Special Relation box at the bottom of the Search form. The Special Relation form appears.
  5. Use the Special Relation form to create a search set relating the Related_Tympanogram_No values in our set of 14 audiograms to the Tympanogram_No field from the Tympanogram table.

  6. This results in a set of 7 tympanograms related to the 14 audiograms. You could now search within this set for Type C tympanograms by double clicking on the set to open the New Query form.

Let's work through another example to illustrate the second type of Special Relation query. In this case, you want to find the first audiogram for all subjects.

To perform a Special Operations query
  1. In the Appendices you would find information on the Audiogram table and, subsequently, the Audiogram_Date field. For subjects with multiple audiograms, the first audiogram will have the earliest Audiogram_Date.
  2. Create a set of all available audiograms by holding down the shift key and double-clicking on the "Create New Set" Item in the Audiogram table list on the Search form.

  3. Next, drag-and-drop this set on the Special Relation box at the bottom of the Search form.


    The Special Relation form appears.
  4. Select the Standard Comparators pop-up and change the selection to Special Operations.
    The Special Operations are applied only to the current set of records in the master table so the related table information disappears.
  5. Group the audiogram records by subject by selecting the Subject_ID field and the "group by" operator.

  6. Add a second line by selecting the Add Line button on the lower right of the Special Relations form.
  7. Select the Audiogram_Date field and the "min value" operator to find the earliest Audiogram_Date for each subject.
  8. Click on Query to create a new set with the first audiograms in it.

In this example, we started with 23 audiograms (Audiogram 001 [23]) and found data on 8 of the first audiograms (Audiogram 002 [8]). The difference in numbers is due to follow-up audiograms.

  1. Find the related 8 subjects by dragging-and-dropping it on the Subject list on the Search form.
  2. View the subjects, by dragging this new set to the Drop Selected Subject Set box at the bottom of the Search form.

You could also identify all the subjects with multiple audiograms by creating two sets of audiograms, the first and the last audiograms, and recombining these sets by placing the "last" audiogram set in Set 1 and the "first" audiogram set in Set 2 then creating a new set from their Difference. Drag the resulting set to the Subjects table.